/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import dto.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Contact DAO
 * @author Hung Cuong <nhc.hcmuns at gmail.com>
 */
public class ContactDAO {
    MSAccessProvider _Provider;

    public ContactDAO() {
        _Provider = new MSAccessProvider();
    }

    /**
     * Insert contact
     * @param contact
     */
    public void insert(ContactDTO contact) {
        String insertQuery = "INSERT INTO Contact(FullName, Company, JobTitle, " +
                                                 "Email, DisplayAs, WebPageAddress, IMAddress) VALUES('" +
                                contact.getFullName() + "', '" +
                                contact.getCompany() + "', '" +
                                contact.getJobTitle() + "', '" +
                                contact.getEmail() + "', '" +
                                contact.getDisplayAs() + "', '" +
                                contact.getWebAddress() + "', '" +
                                contact.getIMAddress() + "')";

        _Provider.excuteNonQuery(insertQuery);
    }

    /**
     * Update contact
     * @param contact
     */
    public void update(ContactDTO contact) {
        String updateQuery = "UPDATE Contact SET " +
                             "FullName='" + contact.getFullName() + "', " +
                             "Company='" + contact.getCompany() + "', " +
                             "JobTitle='" + contact.getJobTitle() + "', " +
                             "Email='" + contact.getEmail() + "', " +
                             "DisplayAs='" + contact.getDisplayAs() + "', " +
                             "WebPageAddress='" + contact.getWebAddress() + "', " +
                             "IMAddress='" + contact.getIMAddress() + "' " +
                             "WHERE ID=" + contact.getId() ;

        _Provider.excuteNonQuery(updateQuery);
    }

    /**
     * Delete contact
     * @param contact
     */
    public void delete(ContactDTO contact) {
        String deleteQuery = "DELETE FROM Contact WHERE ID=" + contact.getId();

        _Provider.excuteNonQuery(deleteQuery);
    }

    /**
     * Search contact
     * @return
     */
    public ArrayList<ContactDTO> search(String fullname, String company,
                                        String jobTitle, String email, String displayAs,
                                        String webpageAddress, String imAddress) {
        
        ArrayList<ContactDTO> list = new ArrayList<ContactDTO>();

        String query = "SELECT * FROM Contact ";
        boolean f1 = true;
        if(!fullname.isEmpty()) {

             if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
            

            query = query + " FullName LIKE '%" + fullname + "%'";

        }
        if(!company.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
            

            query = query + " Company LIKE '%" + company + "%'";

        }
        if(!jobTitle.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
           

            query = query + " JobTitle LIKE '%" + jobTitle + "%'";

        }
        if(!email.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
           

            query = query + " Email LIKE '%" + email + "%'";

        }
        if(!displayAs.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
            

            query = query + " DisplayAs LIKE '%" + displayAs + "%'";

        }
        if(!webpageAddress.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";
            

            query = query + " WebPageAddress LIKE '%" + webpageAddress + "%'";

        }
        if(!imAddress.isEmpty()) {

            if(f1)
             {
                 query += "WHERE ";
                 f1 = false;
             }
            else
                query += "AND ";           

            query = query + " IMAddress LIKE '%" + imAddress + "%'";

        }

        ResultSet rs = _Provider.executeQuery(query);

        if(rs != null){
            try {
                while (rs.next()) {
                    ContactDTO ct = new ContactDTO();

                    ct.setId(rs.getInt(1));
                    ct.setFullName(rs.getString(2));
                    ct.setCompany(rs.getString(3));
                    ct.setJobTitle(rs.getString(4));
                    ct.setEmail(rs.getString(5));
                    ct.setDisplayAs(rs.getString(6));
                    ct.setWebAddress(rs.getString(7));
                    ct.setIMAddress(rs.getString(8));

                    list.add(ct);
                }
            } catch (SQLException ex) {
                Logger.getLogger(ContactDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return list;
    }

    /**
     * Get all contact
     * @return
     * @throws java.sql.SQLException
     */
    public ArrayList<ContactDTO> getAllRecords() {
        ArrayList<ContactDTO> list = new ArrayList<ContactDTO>();

        String selectAllQuery = "SELECT * FROM Contact";

        ResultSet rs = _Provider.executeQuery(selectAllQuery);

        if(rs != null){
            try {
                while (rs.next()) {
                    ContactDTO ct = new ContactDTO();

                    ct.setId(rs.getInt(1));
                    ct.setFullName(rs.getString(2));
                    ct.setCompany(rs.getString(3));
                    ct.setJobTitle(rs.getString(4));
                    ct.setEmail(rs.getString(5));
                    ct.setDisplayAs(rs.getString(6));
                    ct.setWebAddress(rs.getString(7));
                    ct.setIMAddress(rs.getString(8));
                    
                    list.add(ct);
                }
            } catch (SQLException ex) {
                Logger.getLogger(ContactDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        return list;
    }

    /**
     * Get contact information
     * @param contactId
     * @return
     * @throws java.sql.SQLException
     */
    public ContactDTO getInfo(int contactId) {
        ContactDTO ct = new ContactDTO();

        String contactQuery = "SELECT * FROM Contact WHERE Id = " + contactId;

        ResultSet rs = _Provider.executeQuery(contactQuery);

        if(rs != null){
            try {
                rs.next();
                ct.setId(rs.getInt(1));
                ct.setFullName(rs.getString(2));
                ct.setCompany(rs.getString(3));
                ct.setJobTitle(rs.getString(4));
                ct.setEmail(rs.getString(5));
                ct.setDisplayAs(rs.getString(6));
                ct.setWebAddress(rs.getString(7));
                ct.setIMAddress(rs.getString(8));
            } catch (SQLException ex) {
                Logger.getLogger(ContactDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }


        return ct;
    }
}
